package cn.edu.swu.game;

import cn.edu.swu.db.DBEngine;
import cn.edu.swu.db.RecordVisitor;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

public class GameRepo {

    private static GameRepo instance = new GameRepo();

    private GameRepo(){
    }

    public static GameRepo getInstance() {
        return instance;
    }

    public void saveGame(Game game) throws SQLException {
        if (game.getId() > 0) {
            this.updateGame(game);
        } else {
            this.insertGame(game);
        }
    }

    private void insertGame(Game game) throws SQLException {
        String template =
            "INSERT INTO `game`(`name`, `company`, `price`, `describe`, `picture`) " +
            "VALUES (\"%s\", \"%s\", %s, \"%s\", \"%s\")";
        String sql = String.format(
                template, game.getName(), game.getCompany(), game.getPrice(), game.getDescribe(), game.getPicture());
        DBEngine.getInstance().execute(sql);
    }

    private void updateGame(Game game) throws SQLException {
        String template =
            "UPDATE `game` SET `name`=\"%s\", `company`=\"%s\", `price`=%s, `describe`=\"%s\"" +
            "WHERE `id`=%s";
        String sql = String.format(template, game.getName(),
                game.getCompany(), game.getPrice(), game.getDescribe(), game.getId());
        DBEngine.getInstance().execute(sql);
    }

    public void deleteGame(Game game) throws SQLException {
        String template = "DELETE FROM `game` WHERE `id` = %s";
        String sql = String.format(template, game.getId());
        DBEngine.getInstance().execute(sql);
    }

    public void deleteGame(Long id) throws SQLException {
        String template = "DELETE FROM `game` WHERE `id` = %s";
        String sql = String.format(template, id);
        DBEngine.getInstance().execute(sql);
    }

    public List<Game> getAll() throws SQLException {
        String sql = "SELECT `id`, `name`, `company`, `price`, `describe`, `picture` FROM `game`";
        return DBEngine.getInstance().query(sql, new RecordVisitor<Game>() {
            @Override
            public Game visit(ResultSet rs) throws SQLException {
                return GameRepo.getGameFromResultset(rs);
            }
        });
    }

    public Game getById(String id) throws SQLException {
        String sql = "SELECT * FROM `game` WHERE `id` = %s";
        List<Game> games = DBEngine.getInstance().query(
            String.format(sql, id), new RecordVisitor<Game>() {
            @Override
            public Game visit(ResultSet rs) throws SQLException {
                return GameRepo.getGameFromResultset(rs);
            }
        });
        return games.size() == 0 ? null : games.get(0);
    }

    public List<Game> getByIds(List<Long> ids) throws SQLException {
        String sql = "SELECT * FROM `game` WHERE `id` IN (%s)";
        String strId = "";
        for (int i=0; i<ids.size(); i++) {
            strId += ((i > 0) ? "," : "") + ids.get(i);
        }

        System.out.println(String.format(sql, strId));
        List<Game> games = DBEngine.getInstance().query(
            String.format(sql, strId), new RecordVisitor<Game>() {
            @Override
            public Game visit(ResultSet rs) throws SQLException {
                return GameRepo.getGameFromResultset(rs);
            }
        });
        return games;
    }

    private static Game getGameFromResultset(ResultSet rs) throws SQLException {
        Game game = new Game();
        game.setId(rs.getLong("id"));
        game.setName(rs.getString("name"));
        game.setCompany(rs.getString("company"));
        game.setDescribe(rs.getString("describe"));
        game.setPrice(rs.getFloat("price"));
        game.setPicture(rs.getString("picture"));
        return game;
    }
}
